import os
import json
import sqlite3
import datetime


class SceneInfoStorage:
    """场景信息存储和管理类"""

    def __init__(self, db_path="videos.db"):
        """
        初始化存储管理器

        参数:
            db_path (str): SQLite数据库文件路径
        """
        self.db_path = db_path
        self._init_db()

    def _init_db(self):
        """初始化数据库结构"""
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()

        # 创建视频表
        cursor.execute('''
        CREATE TABLE IF NOT EXISTS videos (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            file_path TEXT UNIQUE NOT NULL,
            file_name TEXT NOT NULL,
            width INTEGER,
            height INTEGER,
            fps REAL,
            duration REAL,
            total_frames INTEGER,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
        ''')

        # 创建场景表
        cursor.execute('''
        CREATE TABLE IF NOT EXISTS scenes (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            video_id INTEGER NOT NULL,
            scene_index INTEGER NOT NULL,
            start_frame INTEGER NOT NULL,
            end_frame INTEGER NOT NULL,
            start_time REAL NOT NULL,
            end_time REAL NOT NULL,
            duration REAL NOT NULL,
            output_file_path TEXT,
            keyframe_path TEXT,
            FOREIGN KEY (video_id) REFERENCES videos(id),
            UNIQUE (video_id, scene_index)
        )
        ''')

        conn.commit()
        conn.close()

    def add_video(self, video_path, metadata):
        """
        添加视频信息到数据库

        参数:
            video_path (str): 视频文件路径
            metadata (dict): 视频元数据

        返回:
            int: 插入的视频ID
        """
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()

        file_name = os.path.basename(video_path)

        cursor.execute('''
        INSERT OR REPLACE INTO videos 
        (file_path, file_name, width, height, fps, duration, total_frames) 
        VALUES (?, ?, ?, ?, ?, ?, ?)
        ''', (
            video_path,
            file_name,
            metadata.get('width'),
            metadata.get('height'),
            metadata.get('fps'),
            metadata.get('duration'),
            metadata.get('total_frames')
        ))

        video_id = cursor.lastrowid
        conn.commit()
        conn.close()

        return video_id

    def add_scene(self, video_id, scene_index, scene_data):
        """
        添加场景信息到数据库

        参数:
            video_id (int): 视频ID
            scene_index (int): 场景索引
            scene_data (dict): 场景数据

        返回:
            int: 插入的场景ID
        """
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()

        cursor.execute('''
        INSERT OR REPLACE INTO scenes 
        (video_id, scene_index, start_frame, end_frame, start_time, end_time, 
        duration, output_file_path, keyframe_path) 
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
        ''', (
            video_id,
            scene_index,
            scene_data.get('start_frame'),
            scene_data.get('end_frame'),
            scene_data.get('start_time'),
            scene_data.get('end_time'),
            scene_data.get('duration'),
            scene_data.get('output_file_path'),
            scene_data.get('keyframe_path')
        ))

        scene_id = cursor.lastrowid
        conn.commit()
        conn.close()

        return scene_id

    def get_video_info(self, video_id=None, video_path=None):
        """
        获取视频信息

        参数:
            video_id (int, optional): 视频ID
            video_path (str, optional): 视频路径

        返回:
            dict: 视频信息
        """
        conn = sqlite3.connect(self.db_path)
        conn.row_factory = sqlite3.Row
        cursor = conn.cursor()

        if video_id is not None:
            cursor.execute("SELECT * FROM videos WHERE id = ?", (video_id,))
        elif video_path is not None:
            cursor.execute("SELECT * FROM videos WHERE file_path = ?", (video_path,))
        else:
            conn.close()
            raise ValueError("必须提供video_id或video_path")

        row = cursor.fetchone()
        conn.close()

        if row:
            return dict(row)
        return None

    def get_scenes(self, video_id):
        """
        获取视频的所有场景

        参数:
            video_id (int): 视频ID

        返回:
            list: 场景信息列表
        """
        conn = sqlite3.connect(self.db_path)
        conn.row_factory = sqlite3.Row
        cursor = conn.cursor()

        cursor.execute("""
        SELECT * FROM scenes 
        WHERE video_id = ? 
        ORDER BY scene_index
        """, (video_id,))

        scenes = [dict(row) for row in cursor.fetchall()]
        conn.close()

        return scenes

    def export_scene_info(self, video_id, output_path=None):
        """
        导出视频场景信息为JSON格式

        参数:
            video_id (int): 视频ID
            output_path (str, optional): 输出文件路径

        返回:
            str: 输出文件路径或JSON字符串
        """
        # 获取视频信息
        video_info = self.get_video_info(video_id)
        if not video_info:
            raise ValueError(f"未找到ID为{video_id}的视频")

        # 获取场景信息
        scenes = self.get_scenes(video_id)

        # 创建导出数据
        export_data = {
            "video": video_info,
            "scenes": scenes,
            "exported_at": datetime.datetime.now().isoformat()
        }

        # 导出为JSON
        if output_path:
            with open(output_path, 'w', encoding='utf-8') as f:
                json.dump(export_data, f, indent=2)
            return output_path
        else:
            return json.dumps(export_data, indent=2)